from connsqlite.connect_sqlite3 import Sqlite3Database

"""
 银行与国家关联信息表
"""


class RapidBankManager:
    def __init__(self):
        self.db_name = "rapid.db"
        self.table_name = "rapid_bank_info"
        self.columns = "id INTEGER PRIMARY KEY, " \
                       "bank TEXT(200), " \
                       "phone TEXT(100), " \
                       "website TEXT(100), " \
                       "country_abbr TEXT(100), " \
                       "has_bin TEXT(100), " \
                       "create_time TEXT(100) DEFAULT CURRENT_TIMESTAMP, " \
                       "update_time TEXT(100) DEFAULT CURRENT_TIMESTAMP, " \
                       "CONSTRAINT unique_bank UNIQUE (bank,country_abbr)"
        self.sqlite3 = Sqlite3Database(self.db_name)
        self.sqlite3.create_table(self.table_name, self.columns)

    def selectDataByPager(self, offset, pageSize, query_columns=None, condition=None):
        """
        分页查询列表
        :param condition:
        :param offset: 分页
        :param pageSize: 分页数
        :param query_columns: 需要查询列
        :return:
        """
        limit = f" LIMIT {pageSize} OFFSET {offset} "
        return self.sqlite3.select_data_by_limit(self.table_name, columns=query_columns,
                                                 limit=limit, condition=condition)

    def insertBankdata(self, data: dict):
        """
        字典插入
        :param data:
        :return:
        """
        with self.sqlite3.conn:  # 添加事务
            self.sqlite3.insert_data(self.table_name, data)

    def insertBankByTuple(self, data: tuple, headers=None):
        """
        元组插入
        :param headers:
        :param data:
        :return:
        """
        with self.sqlite3.conn:
            self.sqlite3.insert_data_tuple(self.table_name, data, headers)

    def batchInsertBankByTuple(self, data, headers=None):
        """
        元组批量插入
        :param headers:
        :param data: 需要保存的元组数据
        :return:
        """
        with self.sqlite3.conn:
            self.sqlite3.batch_insert_data_by_tuple(self.table_name, data, headers)

    def selectInfoByBank(self, bank, query_columns=None):
        """
        根据查询
        :return:
        """
        condition = f" bank like '%{bank}%' "
        return self.sqlite3.select_data(self.table_name, columns=query_columns, condition=condition)

    def selectListByCountry(self, country_abbr, query_columns=None):
        """
        根据国家查询列表
        :return:
        """
        condition = f" country_abbr = '{country_abbr}' "
        return self.sqlite3.select_data(self.table_name, columns=query_columns, condition=condition)

    def selectBankByBanks(self, bankList, query_columns=None):
        """
        根据bins查询
        :return:
        """
        banks = tuple(bankList)
        if len(banks) == 1:
            condition = f" bank = '{banks[0]}' "
        else:
            condition = f" bank in {banks} "
        return self.sqlite3.select_data(self.table_name, columns=query_columns, condition=condition)

    def selectListByBank(self, bank, query_columns=None):
        """
        根据bank查询
        :return:
        """
        condition = f" bank = '{bank}' "
        return self.sqlite3.select_data(self.table_name, columns=query_columns, condition=condition)

    def batchDelByBanks(self, bins):
        """
         批量删除BINS
        :param bins: bin的集合
        :return:
        """
        if bins is None or len(bins) == 0:
            return
        binlist = tuple(bins)
        if len(binlist) == 1:
            condition = f" bank = '{binlist[0]}' "
        else:
            condition = f" bank in {binlist} "
        with self.sqlite3.conn:  # 添加事务
            self.sqlite3.delete_data(table_name=self.table_name, condition=condition)

    def selectExportData(self, startTime, endTime):
        """
        导出数据
        :param startTime: 开始时间
        :param endTime: 结束时间
        :return:
        """
        conditon = None
        if startTime is not None and endTime is not None:
            conditon = f" update_time >= '{startTime}' and update_time <= '{endTime}' "
        return self.sqlite3.select_data_by_limit(self.table_name, condition=conditon)

    def updateHasBinStatus(self, bank: str, hasBin: str, countryAbbr: str):
        """
        修改 has_bin
        :param countryAbbr:
        :param bank:
        :param hasBin:
        :return:
        """
        with self.sqlite3.conn:  # 添加事务
            data = {
                'bank': f'{bank}',
                'country_abbr': f'{countryAbbr}',
                'has_bin': f'{hasBin}'
            }
            self.sqlite3.update_data(self.table_name, data,
                                     condition="bank =:bank and country_abbr =:country_abbr")
